package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.FineInfo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Fine table.
 */
public class FineDb extends BaseDb {
    private String insert = "INSERT INTO fine (subject_id, student_id, semester_id, day_present, day_total, daily_rate, description) VALUES (?, ?, ?, ?, ?, ?, ?)";
    private String update = "UPDATE fine SET subject_id = ?, student_id = ?, semester_id = ?, day_present = ?, day_total = ?, daily_rate = ?, description = ? WHERE id = ?";

    /**
     * Insert a Fine into database.
     *
     * @param fine row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(FineInfo fine) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            buildStatement(fine);
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Fine into database.
     *
     * @param fine row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(FineInfo fine) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(fine);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Fines into database.
     *
     * @param fines list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<FineInfo> fines) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (FineInfo fine : fines) {
                doUpdate(fine);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Fine for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public FineInfo select(int id) throws SQLException {
        String select = "SELECT * FROM fine WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Fines.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<FineInfo> select() throws SQLException {
        return select(0, 0, 0);
    }

    /**
     * Get a list of Fines.
     *
     * @param subject_id  the subject's ID to search. Zero for all.
     * @param student_id  the student's ID to search. Zero for all.
     * @param semester_id the semester's ID to search. Zero for all.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<FineInfo> select(int subject_id, int student_id, int semester_id) throws SQLException {
        String select = "SELECT * FROM fine WHERE 1 = 1";
        if (subject_id != 0) select += " AND subject_id = " + subject_id;
        if (student_id != 0) select += " AND student_id = " + student_id;
        if (semester_id != 0) select += " AND semester_id = " + semester_id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<FineInfo> list = new ArrayList<FineInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Compute the day this student present for this subject..
     *
     * @param subject_id  the subject's ID to search.
     * @param student_id  the student's ID to search.
     * @param semester_id the semester's ID to search.
     * @return information found.
     * @throws SQLException if a database access error occurs.
     */
    public TotalPresent compute(int subject_id, int semester_id, int student_id) throws SQLException {
        try {
            connect();
            return new TotalPresent(subject_id, semester_id, student_id).invoke();
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param fine the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(FineInfo fine) throws SQLException {
        buildStatement(fine);
        st.setInt(8, fine.getId());
        st.executeUpdate();
    }

    /**
     * Build the PreparedStatement from a data object.
     *
     * @param fine the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void buildStatement(FineInfo fine) throws SQLException {
        TotalPresent totalPresent = new TotalPresent(fine.getSubjectId(), fine.getSemesterId(), fine.getStudentId()).invoke();

        st.setInt(1, fine.getSubjectId());
        st.setInt(2, fine.getStudentId());
        st.setInt(3, fine.getSemesterId());
        st.setInt(4, totalPresent.getPresent());
        st.setInt(5, totalPresent.getTotal());
        st.setDouble(6, fine.getDailyRate());
        st.setString(7, fine.getDescription());
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private FineInfo getResult() throws SQLException {
        FineInfo fine = new FineInfo();
        fine.setId(rs.getInt(1));
        fine.setSubjectId(rs.getInt(2));
        fine.setStudentId(rs.getInt(3));
        fine.setSemesterId(rs.getInt(4));
        fine.setDayPresent(rs.getInt(5));
        fine.setDayTotal(rs.getInt(6));
        fine.setDailyRate(rs.getDouble(7));
        fine.setDescription(rs.getString(8));

        return fine;
    }

    /**
     * Compute the day this student present for this subject.
     */
    public class TotalPresent {
        private int subject_id;
        private int semester_id;
        private int student_id;
        private int present;
        private int total;

        public TotalPresent(int subject_id, int semester_id, int student_id) {
            this.subject_id = subject_id;
            this.semester_id = semester_id;
            this.student_id = student_id;
        }

        public int getPresent() {
            return present;
        }

        public int getTotal() {
            return total;
        }

        private TotalPresent invoke() throws SQLException {
            Statement stmt = null;
            ResultSet rs = null;
            present = 0;
            total = 0;

            try {
                stmt = con.createStatement();
                rs = stmt.executeQuery(new StringBuilder()
                        .append("SELECT present FROM attendance a")
                        .append(" JOIN schedule sc ON a.schedule_id = sc.id ")
                        .append(" JOIN students st ON sc.class_id = st.class_id AND a.student_id = st.id")
                        .append(" WHERE sc.subject_id = ").append(subject_id)
                        .append(" AND a.student_id = ").append(student_id)
                        .append(" AND start_time >= (SELECT start_date FROM semesters WHERE id = ").append(semester_id).append(")")
                        .append(" AND end_time <= (SELECT end_date FROM semesters WHERE id = ").append(semester_id).append(")")
                        .toString());

                while (rs.next()) {
                    if (rs.getBoolean(1)) present++;
                    total++;
                }
            } finally {
                if (stmt != null) stmt.close();
                if (rs != null) rs.close();
            }
            return this;
        }
    }
}
